import pandas

'''
    VisqlConn:Visql驱动连接
    
        select_data:查询数据
        insert_data:增加数据
        change_data:改变数据
        delete_data:删除数据
        
        print_data:打印数据结构
        
        serial_num:自增序号
'''


def create_sql(db_name, index, columns, values):
    # 创建本地数据
    df = pandas.DataFrame(values, index=index, columns=columns)
    df.to_csv("./" + db_name + ".csv", header=True, index=True)


class VisqlConn(object):
    def __init__(self, db="df"):
        self.db_name = db
        self.df_db = pandas.DataFrame()
        self.serial_num = 0

        self.__refresh()

    def __enter__(self):
        return self

    def __exit__(self, my_type, value, trace):
        self.do_close()

    def __len__(self):
        return len(self.df_db)

    def print_data(self):
        print(self.df_db)

    def select_data(self, where=None):
        # 语法：['姓名'] > '10'
        if where:
            return eval("self.df_db[self.df_db{}].values".format(where))
        return self.df_db.values

    def insert_data(self, data):
        self.serial_num += 1
        self.df_db.loc[self.serial_num] = data

    def change_data(self, row, col, val):
        self.df_db.loc[row, col] = val

    def delete_data(self, where=None, index=None):
        try:
            if index is not None:
                self.df_db = self.df_db.drop(index)
            elif where is not None:
                indexes = eval("self.df_db[self.df_db{}].index.values".format(where))
                for i in indexes:
                    self.df_db = self.df_db.drop(i)
            else:
                return False
            return True
        except Exception as e:
            print(e)
            return False

    def __refresh(self):
        self.df_db = pandas.read_csv("./" + self.db_name + ".csv", header=0, prefix="x", names=None, index_col=0)
        sn = self.df_db.index
        self.serial_num = 0 if len(sn) == 0 else sn[-1]

    def __storage(self):
        self.df_db.to_csv("./" + self.db_name + ".csv")

    def do_close(self):
        self.__storage()


if __name__ == "__main__":
    create_sql("df", ["1", "2", "3"], ["姓名", "年龄", "住址"], [["1", "2", "3"], ["4", "5", "6"], ["7", "8", "9"]])
    with VisqlConn(db="df") as visql:
        print(visql.select_data("['年龄'] > 5"))
        visql.insert_data([8, 8, 8])
        print(visql.select_data())
        visql.change_data(3, "姓名", 23)
        print(visql.select_data())
        visql.delete_data(where="['年龄'] > 2")
        visql.print_data()
